﻿using System.Collections.Generic;
using System.Data;
using System.Linq;
using Core.Library;
using DataObject.Category;
using Microsoft.ApplicationBlocks.Data;

namespace DataObject.Product
{
    public class ProductDAL
    {
        /***************************************
        = Back End
        -------------------------------------- */
        public static void Product_Create(ProductObject o)
        {
            SqlHelper.ExecuteScalar(Constants.ConnectionString, "Product_Create",
                                        o.CategoryId, o.Status, o.Type, o.Code, o.Name, o.Description, o.ImagePath, o.Images, o.Content, o.UnitPrice, o.SpecialPrice, o.CreatedBy);
        }

        public static void Product_Update(ProductObject o)
        {
            SqlHelper.ExecuteScalar(Constants.ConnectionString, "Product_Update",
                                       o.ProductId, o.CategoryId, o.Status, o.Type, o.Code, o.Name, o.Description, o.ImagePath, o.Images, o.Content, o.UnitPrice, o.SpecialPrice, o.EditedBy);
        }

        public static void Product_Update_Status(int id, int status)
        {
            SqlHelper.ExecuteScalar(Constants.ConnectionString, "Product_Update_Status", id, status);
        }

        public static void Product_Update_ImagePath(int id, string imgPath)
        {
            SqlHelper.ExecuteScalar(Constants.ConnectionString, "Product_Update_ImagePath", id, imgPath);
        }

        public static void Product_Delete(int productId)
        {
            SqlHelper.ExecuteScalar(Constants.ConnectionString, "Product_Delete", productId);
        }

        public static IDataReader Product_GetById(int productId)
        {
            return SqlHelper.ExecuteReader(Constants.ConnectionString, "Product_GetById", productId);
        }

        public static int Product_GetStatistic(int status)
        {
            return (int)SqlHelper.ExecuteScalar(Constants.ConnectionString, "Product_GetStatistic", status);
        }


        /***************************************
        = Front End
        -------------------------------------- */
        public static DataTable Product_ListTopHightlightProduct(int count)
        {
            return SqlHelper.ExecuteDataset(Constants.ConnectionString, "Product_ListTopHightlightProduct", 
                                                new object[] { count }).Tables[0];
        }

        public static DataTable Product_TopProductByCategory(int categoryId, int count)
        {
            return SqlHelper.ExecuteDataset(Constants.ConnectionString, "Product_TopProductByCategory", 
                                                new object[] { categoryId, count} ).Tables[0];
        }

        public static DataTable Product_ListPaginationByCategory(string keyword, int categoryId, int status, int type, int pageIndex, int pageSize, ref int totalRecords)
        {
            var filter  = "1=1";
            var strId   = string.Empty;

            // Keyword
            if (!string.IsNullOrEmpty(keyword))
                filter += string.Format(" AND (Name LIKE N'%{0}%' OR Code LIKE N'%{0}%')", keyword);

            // Type
            if (type > 0)
                filter += string.Format(" AND Type = {0}", type);    

            // CategoryId
            var o = CategoryController.Category_GetById(categoryId);
            if (o != null)
            {
                if (o.ParentId == Constants.PARENT_ROOT)
                {
                    var lstId = new List<int>();
                    var lstCategory = CategoryController.Category_GetByParentId((int)CategoryType.Product, categoryId, Constants.ACTIVE);
                    if (lstCategory != null && lstCategory.Count > 0)
                        lstId.AddRange(lstCategory.Select(t => t.CategoryId));

                    if (lstId.Count > 0)
                        strId = lstId.Aggregate(strId, (current, t) => current + t + " ,");

                    if (!string.IsNullOrEmpty(strId))
                    {
                        strId = strId.Substring(0, strId.Length - 2);
                        filter += string.Format(" AND CategoryId in ({0})", strId);
                    }
                }
                else
                    filter += string.Format(" AND CategoryId = {0}", categoryId);
            }

            // Status
            filter += status > 0
                          ? string.Format(" AND [Status] & {0} = {0}", status)
                          : string.Format(" AND ([Status] & {0} = {0} OR [Status] & {1} = {1} OR [Status] & {2} = {2})",
                                          (int) ProductStatus.IsDraft, (int) ProductStatus.IsPublished, (int) ProductStatus.Highlight);


            totalRecords = Action.GetListPagingCount("Product", filter);
            return Action.DataTable_GetListPaging("Product", "ProductId DESC", Constants.Product_FieldList, filter, pageIndex, pageSize);
        }        

        public static DataTable Product_ListPaginationRelatedProduct(int categoryId, int productId, int pageIndex, int pageSize, ref int totalRecords)
        {
            var filter = string.Format("([Status] & 2 = 2 OR [Status] & 4 = 4) AND CategoryId = {0} AND ProductId < {1}", 
                                            categoryId, productId);

            totalRecords = Action.GetListPagingCount("Product", filter);
            return Action.DataTable_GetListPaging("Product", "ProductId DESC", 
                                                    Constants.Product_FieldList, filter, pageIndex, pageSize, ref totalRecords);
        }
    }
}
